package com.ruoyi.weiriya.mapper;

import com.ruoyi.weiriya.domain.vo.StatisticalReportVo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 公司信息Mapper接口
 * 
 * @author lynn
 * @date 2023-08-01
 */
public interface WStatisticalReportMapper
{

    @Select("select IFNULL(vt.type_value,'未知') as `key`, count(m.id) as `value`\n" +
            "FROM w_member m\n" +
            "LEFT JOIN w_vehicle v on v.member_id = m.id\n" +
            "LEFT JOIN w_vehicle_charge_type vt on vt.id = v.vehicle_charge_type_id\n" +
            "GROUP BY v.vehicle_charge_type_id")
    List<StatisticalReportVo> statisticalMemberNumByVehicleType();

    @Select("select IFNULL(a.name,'未知') as `key`, count(m.id) as `value`\n" +
            "FROM w_member m\n" +
            "LEFT JOIN w_area a on m.area_id = a.id\n" +
            "GROUP BY m.area_id")
    List<StatisticalReportVo> statisticalMemberNumByArea();

    @Select("select DATE_FORMAT(complete_time,#{param3}) `key`, sum(final_price) value\n" +
            "FROM w_order \n" +
            "WHERE order_status = 'PAID'\n" +
            "and complete_time >= #{param1} and complete_time <= #{param2}\n" +
            "GROUP BY DATE_FORMAT(complete_time,#{param3})")
    List<StatisticalReportVo> statisticalIncome(String startTime, String endTime, String format);

    @Select("<script>" +
            "select DATE_FORMAT(complete_time,#{format}) `key`, count(1) value\n" +
            "FROM w_order \n" +
            "WHERE order_status = 'PAID'\n" +
            "and complete_time &gt;= #{startTime} and complete_time &lt;= #{endTime}\n" +
            "            <if test=\"orderType != null and orderType != ''\"> " +
            "                and member_type = #{orderType} " +
            "            </if>" +
            "GROUP BY DATE_FORMAT(complete_time,#{format}) " +
            "</script>")
    List<StatisticalReportVo> statisticalOrder(@Param("startTime") String startTime, @Param("endTime") String endTime, @Param("format") String format, @Param("orderType") String orderType);

    @Select("<script>" +
            "select DATE_FORMAT(create_time,#{format}) `key`, count(1) value\n" +
            "FROM w_member_charge_record \n" +
            "WHERE create_time &gt;= #{startTime} and create_time &lt;= #{endTime}\n" +
            "            <if test=\"recordType != null and recordType == 1\"> " +
            "                and vip_type != 4 " +
            "            </if>" +
            "            <if test=\"recordType != null and recordType == 2\"> " +
            "                and vip_type = 4 " +
            "            </if>" +
            "GROUP BY DATE_FORMAT(create_time,#{format}) " +
            "</script>")
    List<StatisticalReportVo> statisticalChargeRecord(@Param("startTime") String startTime, @Param("endTime") String endTime, @Param("format") String format, @Param("recordType") String recordType);

    @Select("<script>" +
                "select DATE_FORMAT(create_time,#{format}) `key`, count(1) value\n" +
                "FROM w_member_deposit_record \n" +
                "WHERE create_time &gt;= #{startTime} and create_time &lt;= #{endTime}\n" +
                "            <if test=\"recordType != null and recordType == 1\"> " +
                "                and vip_type != 4 " +
                "            </if>" +
                "            <if test=\"recordType != null and recordType == 2\"> " +
                "                and vip_type = 4 " +
                "            </if>" +
                "GROUP BY DATE_FORMAT(create_time,#{format}) " +
                "</script>")
    List<StatisticalReportVo> statisticalDepositRecord(@Param("startTime") String startTime, @Param("endTime") String endTime, @Param("format") String format, @Param("recordType") String recordType);

    @Select("SELECT DATE_FORMAT( end_time, #{param3} ) `key`, SUM( amount ) 'value' \n" +
            "FROM ( SELECT amount, end_time \n" +
            "\tFROM `w_member_deposit_record` \n" +
            "\tWHERE end_time >= #{param1} and end_time <= #{param2} \n" +
            "\tUNION \n" +
            "\tSELECT amount, end_time \n" +
            "\tFROM `w_member_charge_record` \n" +
            "\tWHERE end_time >= #{param1} and end_time <= #{param2} ) t \n" +
            "GROUP BY DATE_FORMAT(end_time,#{param3})")
    List<StatisticalReportVo> statisticalConsume(String startTime, String endTime, String s);
}
